package edu.fub.pub2search.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Dealing with retrieving data in a database (Read-only).
 * 
 * @author Josef
 */
public class DataAccess {
	
	/** MySQL connection */
	private Connection conn;
	
	/** Pub attribute constant */
	public static final int PUB_ID = 1;
	public static final int PUB_NAME = 2;
	public static final int PUB_ADDRESS = 3;
	public static final int PUB_DESCRIPTION = 4;
	public static final int PUB_FACILITY = 5;
	public static final int PUB_OPEN_HOUR = 6;
	public static final int PUB_CLOSE_HOUR = 7;
	public static final int PUB_LATITUDE = 8;
	public static final int PUB_LONGITUDE = 9;
	
	/**
	 * Default constructor
	 * 
	 * @param conn The connection object.
	 */
	public DataAccess(Connection conn) {
		this.conn = conn;
	}
	
	/**
	 * Queries the pubs within the given coordinates.
	 * 
	 * @param x1 The first latitude.
	 * @param x2 The second latitude. 
	 * @param y1 The first longitude.
	 * @param y2 The second longitude.
	 * @return A <i>ResultSet</i> of pubs data.
	 */
	public ResultSet queryPub(int x1, int x2, int y1, int y2) {
		
		/* 
		 * NOTE: There are some important constraints, that are:
		 *  - lat1 should be bigger than lat2
		 *  - lng1 should be bigger than lng2 
		 */
		int lat1, lat2, lng1, lng2;
		
		if (x1 >= x2) { lat1 = x1; lat2 = x2; }
		else { lat1 = x2; lat2 = x1; }
		
		if (y1 >= y2) { lng1 = y1; lng2 = y2; }
		else { lng1 = y2; lng2 = y1; }
		
		ResultSet result = null;		
		try {
			// get pub candidates within coordinate.
			String query = "SELECT id, name, address, description, facility, open_hour, close_hour, latitude, longitude " +
						   "FROM Pub " +
						   "WHERE latitude <= ? AND latitude >= ? " + 
						   "AND longitude <= ? AND longitude >= ?";
			
			// set as prepared statement
			PreparedStatement stmt = conn.prepareStatement(query);
			
			stmt.setInt(1, lat1);
			stmt.setInt(2, lat2);
			stmt.setInt(3, lng1);
			stmt.setInt(4, lng2);
			
			// begin querying
			result = stmt.executeQuery();
		 	
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
				
		return result;
	}
}
